package cn.dmrliu.edu.publish.mapper;

import cn.dmrliu.edu.publish.bean.ToolExamCourseStats;
import cn.dmrliu.edu.publish.bean.ToolExamPaperStats;
import cn.dmrliu.edu.publish.bean.ToolExamQuestionStats;
import cn.dmrliu.edu.publish.bean.ToolExamScoreStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ToolExamMapper {

    @Select("select\n" +
            "  paper_id,\n" +
            "  paper_name,\n" +
            "  sum(user_ct) user_ct,\n" +
            "  sum(score_sum) score_sum,\n" +
            "  sum(dur_sum) dur_sum\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by paper_id,paper_name\n" +
            "order by paper_id;")
    List<ToolExamPaperStats> getPaperStats(Integer date);


    @Select("select\n" +
            "  course_id,\n" +
            "  course_name,\n" +
            "  sum(user_ct) user_ct,\n" +
            "  sum(score_sum) score_sum,\n" +
            "  sum(dur_sum) dur_sum\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by course_id,course_name\n" +
            "order by course_id;")
    List<ToolExamCourseStats> getCourseStats(Integer date);


    @Select("select\n" +
            "  1 flag,\n" +
            "  sum(arrayElement(score_sec,1)) userCt\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "union all\n" +
            "select\n" +
            "  2,\n" +
            "  sum(arrayElement(score_sec,2))\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "union all\n" +
            "select\n" +
            "  3,\n" +
            "  sum(arrayElement(score_sec,3))\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "union all\n" +
            "select\n" +
            "  4,\n" +
            "  sum(arrayElement(score_sec,4))\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "union all\n" +
            "select\n" +
            "  5,\n" +
            "  sum(arrayElement(score_sec,5))\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "union all\n" +
            "select\n" +
            "  6,\n" +
            "  sum(arrayElement(score_sec,6))\n" +
            "from dws_tool_exam_page_window\n" +
            "where toYYYYMMDD(stt) = #{date}")
    List<ToolExamScoreStats> getScoreStats(Integer date);

    @Select("select\n" +
            "  question_id,\n" +
            "  sum(current_ct) current_ct,\n" +
            "  sum(answer_ct) answer_ct,\n" +
            "  sum(current_uvct) current_uv_ct,\n" +
            "  sum(answer_uvct) answer_uv_ct\n" +
            "from dws_tool_exam_question_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by question_id\n" +
            "order by question_id;")
    List<ToolExamQuestionStats> getQuestionStats(Integer date);
}
